﻿using Coldairarrow.Business.PB;
using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.PB;
using Coldairarrow.Entity.TD;
using Coldairarrow.Util;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Threading.Tasks;

namespace Coldairarrow.Business.TD
{
    public partial class TD_SendBusiness : BaseBusiness<TD_Send>, ITD_SendBusiness, ITransientDependency
    {
        //, IPB_StockBusiness StockBusiness
        public TD_SendBusiness(IDbAccessor db)
            : base(db)
        {
            //_StockBusiness = StockBusiness;
        }
        //IPB_StockBusiness _StockBusiness;
        #region 外部接口

        //public async Task<PageResult<TD_Send>> GetDataListAsync(PageInput<ConditionDTO> input)
        //{
        //    var q = GetIQueryable();
        //    var where = LinqHelper.True<TD_Send>();
        //    var search = input.Search;

        //    //筛选
        //    if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
        //    {
        //        var newWhere = DynamicExpressionParser.ParseLambda<TD_Send, bool>(
        //            ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
        //        where = where.And(newWhere);
        //    }

        //    return await q.Where(where).GetPageResultAsync(input);
        //}

        //public async Task<TD_Send> GetTheDataAsync(string id)
        //{
        //    return await GetEntityAsync(id);
        //}

        //public async Task AddDataAsync(TD_Send data)
        //{
        //    await InsertAsync(data);
        //}

        //public async Task UpdateDataAsync(TD_Send data)
        //{
        //    await UpdateAsync(data);
        //}

        [DataDeleteLog(UserLogType.发货管理, "Code", "发货单")]
        public async Task DeleteDataAsync(List<string> ids)
        {
            await DeleteAsync(ids);
        }
        /// <summary>
        /// 获取自动备货数据 
        /// 1.事务（有了）
        /// 2.换更新方式：（批量跟新）这样一条更新太慢了     
        /// 3.处理之前判断 数据库里面的物料够不够发货通知单的 不够就不需要处理 
        /// 4.加数据锁
        /// 5.货位编码排序 也有正序倒叙
        /// 6.全局参数（）
        /// 7.修改编辑模式:增加数据库字段:来标识一家备货了多少了。
        /// 8.数据库设计修改入库有一个表出库有一个表。实时数据表 表名进行修改
        /// </summary>
        /// <param name="NoticeId"></param>
        /// <param name="Type"></param>
        /// <returns></returns>
        private static object objlock = new object();


        //[Transactional]
        public async Task<DistributionDataAjaxResult> DistributionData(string NoticeId, StocktypeIn Stocktype)
        {
            lock (objlock)
            {
                PB_Stock data = new PB_Stock();
                data.Style = 2;
                // 获取 需要配货的数据
                var detailedList = Db.GetListBySql<DistributionDataDTO>(@"SELECT   d.Id PB_DesignListInfo,c.Id DesignListId,d.Nums,d.RealNums,b.MaterialId,b.EstimatedQuantity  FROM PB_WarehousingNotice a 
INNER JOIN PB_ShippingList b on a.Id = b.OddNumber
INNER JOIN PB_DesignDetail d on b.SubId = d.Id
INNER JOIN PB_DesignList c on d.ParentId = c.Id
WHERE   b.ParentId  = '" + NoticeId + "'; ");
                #region    不同的 type  接不同的sql 
                string sql = @"SELECT
	IFNULL(g.StockNumber,0) StockNumber,
	d.Id,
	m.`Code` MateriaCode,
	m.`Spec` MaterialSpec,
	g.AreaId,
	g.StorId RackId,
	g.LocalId LocationId,
	d.Id PB_DesignListInfo,
	d.Slot Slot,
	c.Id DesignListId,
	a.Id BatchNumber,
	d.Nums,
	d.RealNums ShouldNumber,
	d.MaterialId,
	b.EstimatedQuantity,
	m.`Name` MaterialName,
	g.LocationName,
	g.StorageName,
	g.StorAreaName,
	g.RackName,
	m.Spec,
	m.ProductCode
FROM
	PB_WarehousingNotice a
	INNER JOIN PB_ShippingList b ON a.Id = b.OddNumber
	LEFT  JOIN PB_DesignDetail d ON b.SubId = d.Id
	LEFT JOIN PB_DesignList c ON d.ParentId = c.Id
	LEFT JOIN (
	SELECT
		a.Id,
		m.`Code`,
		m.`Spec`,
		c.AreaId,
		c.StorId,
		a.LocalId,
			IFNULL(( a.Nums_N + a.Nums_O - a.Nums_R ),0) StockNumber,
		a.MaterialId MaterialId,
		m.`Name` MaterialName,
	m.`Spec` MaterialSpec,
		c.`Name` LocationName,
		d.`Name` StorageName,
		e.`Name` StorAreaName ,
		rack.`Name` RackName
	FROM
		IT_LocalMaterial a
		INNER JOIN PB_Material m ON a.MaterialId = m.Id
		LEFT JOIN PB_Location c ON a.LocalId = c.id
		LEFT JOIN ( SELECT Id, `Name` FROM Base_Department WHERE Type = 2 ) d ON c.StorId = d.id
		LEFT JOIN PB_StorArea e ON c.AreaId = e.id 
			LEFT JOIN PB_Rack rack ON c.RackId = rack.id 
	) g ON b.MaterialId = g.MaterialId
	LEFT JOIN PB_Material m ON d.MaterialId = m.Id 
WHERE 1=1  AND b.ParentId='" + NoticeId + "' ";
                switch (Stocktype)
                {
                    case StocktypeIn.FirstIn:
                        sql = sql + " ORDER BY d.CreateTime DESC";
                        break;
                    case StocktypeIn.FirstOut:
                        sql = sql + " ORDER BY d.CreateTime asc";
                        break;
                    case StocktypeIn.CargoSpaceorder:
                        sql = sql + "  order by d.`Slot`";
                        break;
                    default:
                        return null;
                }
                //仓库里面有的物料数量
                var InventoryData = Db.GetListBySql<DistributionDataDTO>(sql);

                if (InventoryData?.Count > 0)
                {
                    //AND g.StockNumber = '0.0000'
                    //var info = InventoryData.Where(P => P.StockNumber == 0).ToList();
                    //if (info?.Count > 0)
                    //{
                    //    DistributionDataAjaxResult resone = new DistributionDataAjaxResult
                    //    {
                    //        Success = false,
                    //        Msg = "库存量不足！无法自动备货"
                    //    };
                    //    return resone;
                    //}
                }
                else
                {
                    DistributionDataAjaxResult restow = new DistributionDataAjaxResult
                    {
                        Success = false,
                        Msg = "库存量不足！无法自动备货"
                    };
                    return restow;
                }



                #endregion

                List<InventoryDataDTO> distributionDataList = new List<InventoryDataDTO>();
                InventoryDataDTO distributionDataDTO = new InventoryDataDTO();

                for (int i = 0; i < detailedList.Count; i++)
                {
                    for (int j = 0; j < InventoryData.Count; j++)
                    {
                        if (InventoryData[j].StockNumber == 0)
                        {
                            continue;
                        }
                        //如果物料一样
                        if (detailedList[i].MaterialId == InventoryData[j].MaterialId)
                        {
                            //如果需要发货量大于仓库这一列的量
                            if (detailedList[i].EstimatedQuantity > InventoryData[j].StockNumber)
                            {
                                //需要量减去准备发货量
                                detailedList[i].EstimatedQuantity = detailedList[i].EstimatedQuantity - InventoryData[j].StockNumber;
                                //仓库里面数量变成0
                                InventoryData[j].StockNumber = 0;
                                //distributionDataDTO.SumStockVolume = InventoryData[j].StockNumber;
                                data.Id = InventoryData[j].Id;
                                //修改仓库备货中
                                //await _StockBusiness.UpdateDataAsync(data);
                            }
                            else if (detailedList[i].EstimatedQuantity <= InventoryData[j].StockNumber)
                            {
                                //需要使用 仓库shu
                                detailedList[i].StockNumber = detailedList[i].StockNumber - InventoryData[j].EstimatedQuantity;
                                //distributionDataDTO.SumStockVolume = InventoryData[j].StockNumber;
                                data.Id = InventoryData[j].Id;
                                //修改仓库备货中
                                //await _StockBusiness.UpdateDataAsync(data);
                                distributionDataDTO.MaterialId = detailedList[i].MaterialId;
                                distributionDataDTO.MaterialName = InventoryData[j].MaterialName;
                                distributionDataDTO.LocationName = InventoryData[j].LocationName;
                                distributionDataDTO.StorageName = InventoryData[j].StorageName;
                                distributionDataDTO.StorAreaName = InventoryData[j].StorAreaName;
                                distributionDataDTO.EstimatedQuantity = detailedList[i].EstimatedQuantity;
                                distributionDataDTO.StockNumber = InventoryData[j].StockNumber;
                                //distributionDataDTO.Nums = detailedList[i].Nums;
                                //distributionDataDTO.RealNums = detailedList[i].RealNums;
                                //distributionDataDTO.ReadyNums = InventoryData[j].ReadyNums;
                                //distributionDataDTO.ShippedNums = InventoryData[j].ShippedNums;
                                distributionDataList.Add(distributionDataDTO);
                                break;
                            }
                            else
                            {
                                InventoryData[j].EstimatedQuantity = 0;
                                detailedList[i].StockNumber = 0;
                                //distributionDataDTO.SumStockVolume = InventoryData[j].StockNumber;
                                data.Id = InventoryData[j].Id;
                                //修改仓库备货中
                                //await _StockBusiness.UpdateDataAsync(data);
                            }
                            distributionDataDTO.MaterialId = detailedList[i].MaterialId;
                            distributionDataDTO.MaterialName = InventoryData[j].MaterialName;
                            distributionDataDTO.LocationName = InventoryData[j].LocationName;
                            distributionDataDTO.StorageName = InventoryData[j].StorageName;
                            distributionDataDTO.StorAreaName = InventoryData[j].StorAreaName;
                            distributionDataDTO.EstimatedQuantity = detailedList[i].EstimatedQuantity;
                            distributionDataDTO.StockNumber = InventoryData[j].StockNumber;
                            //distributionDataDTO.Nums = detailedList[i].Nums;
                            //distributionDataDTO.RealNums = detailedList[i].RealNums;
                            //distributionDataDTO.ReadyNums = InventoryData[j].ReadyNums;
                            //distributionDataDTO.ShippedNums = InventoryData[j].ShippedNums;
                            distributionDataList.Add(distributionDataDTO);
                        }
                    }
                }
                DistributionDataAjaxResult res = new DistributionDataAjaxResult
                {
                    Success = true,
                    Msg = "备货成功",
                    dateInfo = distributionDataList
                };
                return res;
            }
        }
        /// <summary>
        /// 朱柏清 2022-03-10 自动备货
        /// </summary>
        /// <param name="NoticeId"></param>
        /// <param name="Stocktype"></param>
        /// <returns></returns>
        public async Task<List<PickUpMaterialDTO>> AutomaticStockData(string NoticeId, StocktypeIn Stocktype)
        {
            string sqlstr = @"select * from (
select t1.Id,t1.MaterialId,(t1.RealNums-IFNULL(c.StockVolume,0)) as ShouldNumber from (
select Id,DesignId,MaterialId,SUM(Nums) as Nums,SUM(RealNums) as RealNums from (
select a.Id,b.ParentId as DesignId,b.MaterialId,b.Nums,b.RealNums from PB_DeliveryNoticeItem as a left join PB_DesignDetail as b on a.DesignId=b.ParentId where b.State!=2
UNION ALL
select a.Id,b.ParentId as DesignId,b.MaterialId,-b.Nums as Nums,-b.RealNums as RealNums from PB_DeliveryNoticeItem as a left join PB_DesignDetail as b on a.DesignId=b.ParentId where b.State=2
) as t where Id='" + NoticeId + "' group by Id,DesignId,MaterialId ) as t1 left join ( select a.ParentId,b.MaterialId,SUM(IFNULL(case WHEN a.Status>=3 then b.ReadyNums else b.StockVolume end,0)) as StockVolume FROM PB_PickUpMain as a left join PB_PickUpList as b on a.Id=b.ParentId where a.ParentId= '" + NoticeId + "' group by a.ParentId,b.MaterialId ) as c on t1.Id=c.ParentId and t1.MaterialId=c.MaterialId  ) as tb where ShouldNumber>0 ";
            var detailedList = Db.GetListBySql<DistributionDataDTO>(sqlstr);
            string sql = @"select *,concat(PB_DesignListInfo,'_',LocationId) as onlyId from (
select t1.*,IFNULL(lm.ScanNum,0) as ScanNum,(IFNULL(lm.ScanNum,0)-IFNULL(lm.Nums_R,0)) as StockNumber,lm.CreateTime,lm.LocalId as LocationId,lm.StorId,IFNULL(c.StockVolume,0) as StockVolume,dt.`Name` as StorageName,l.StorAreaName,m.`Code` as MaterialCode,m.Spec as MaterialSpec,m.`Name` as MaterialName
,case when (t1.RealNums-IFNULL(c.StockVolume,0))<0 THEN 0 else (t1.RealNums-IFNULL(c.StockVolume,0)) end as ShouldNumber
 from (
select NoticeId,DesignId,MaterialId,SUM(Nums) as Nums,SUM(RealNums) as RealNums,(
										SELECT
											`PB_DesignDetail`.`Id` 
										FROM
											`PB_DesignDetail` 
										WHERE
											((
													`PB_DesignDetail`.`State` <> 2 
													) 
												AND ( `PB_DesignDetail`.`ParentId` = `t`.`DesignId` ) 
											AND ( `PB_DesignDetail`.`MaterialId` = `t`.`MaterialId` )) 
										ORDER BY
											`PB_DesignDetail`.`CreateTime` 
											LIMIT 1 
										) AS PB_DesignListInfo from (
select a.Id as NoticeId,b.ParentId as DesignId,b.MaterialId,b.Nums,b.RealNums from PB_DeliveryNoticeItem as a left join PB_DesignDetail as b on a.DesignId=b.ParentId where b.State!=2
UNION ALL
select a.Id as NoticeId,b.ParentId as DesignId,b.MaterialId,-b.Nums as Nums,-b.RealNums as RealNums from PB_DeliveryNoticeItem as a left join PB_DesignDetail as b on a.DesignId=b.ParentId where b.State=2
) as t where NoticeId='{0}' group by NoticeId,DesignId,MaterialId 
) as t1 
left join IT_LocalMaterial as lm on t1.MaterialId=lm.MaterialId 
left join
 ( 
select a.ParentId,b.MaterialId,SUM(IFNULL(b.ReadyNums,0)) as StockVolume FROM PB_PickUpMain as a 
left join PB_PickUpList as b on a.Id=b.ParentId 
where a.ParentId= '{0}' and a.`Status`=3 group by a.ParentId,b.MaterialId
) as c on t1.NoticeId=c.ParentId and t1.MaterialId=c.MaterialId 
left join Base_Department as dt on lm.StorId=dt.Id 
left join View_StorAreaRackLocation as l on lm.LocalId=l.Id 
left join PB_Material as m on t1.MaterialId=m.Id 
)as tb where StockNumber>0  ";
            switch (Stocktype)
            {
                case StocktypeIn.FirstIn:
                    sql = sql + " ORDER BY CreateTime DESC";
                    break;
                case StocktypeIn.FirstOut:
                    sql = sql + " ORDER BY CreateTime asc";
                    break;
                default:
                    return null;
            }
            string sqlMain = string.Format(sql, NoticeId);
            var InventoryData = Db.GetListBySql<PickUpMaterialDTO>(sqlMain);
            if (InventoryData == null)
            {
                throw new BusException("库存量不足！无法自动备货");
            }
            List<PickUpMaterialDTO> distributionDataList = new List<PickUpMaterialDTO>();
            foreach (var item in detailedList)
            {
                //当前物料剩余应备数量
                double? ShouldNumber = item.ShouldNumber;
                var inventMaterialdata = InventoryData.Where(x => x.MaterialId == item.MaterialId).ToList();
                if (inventMaterialdata.Count > 0)
                {
                    foreach (var itemdata in inventMaterialdata)
                    {
                        if (ShouldNumber > 0)
                        {
                            PickUpMaterialDTO distributionDataDTO = new PickUpMaterialDTO();
                            distributionDataDTO.NoticeId = itemdata.NoticeId;
                            distributionDataDTO.DesignId = itemdata.DesignId;
                            distributionDataDTO.MaterialId = itemdata.MaterialId;
                            distributionDataDTO.Nums = itemdata.Nums;
                            distributionDataDTO.RealNums = itemdata.RealNums;
                            distributionDataDTO.PB_DesignListInfo = itemdata.PB_DesignListInfo;
                            distributionDataDTO.ScanNum = itemdata.ScanNum;
                            distributionDataDTO.StockNumber = itemdata.StockNumber;
                            distributionDataDTO.CreateTime = itemdata.CreateTime;
                            distributionDataDTO.LocationId = itemdata.LocationId;
                            distributionDataDTO.StorId = itemdata.StorId;
                            distributionDataDTO.StorageName = itemdata.StorageName;
                            distributionDataDTO.StorAreaName = itemdata.StorAreaName;
                            distributionDataDTO.MaterialCode = itemdata.MaterialCode;
                            distributionDataDTO.MaterialSpec = itemdata.MaterialSpec;
                            distributionDataDTO.MaterialName = itemdata.MaterialName;
                            distributionDataDTO.ShouldNumber = itemdata.ShouldNumber;
                            distributionDataDTO.onlyId = itemdata.onlyId;
                            //剩余备货数量小于等于库存可用量
                            if (ShouldNumber <= itemdata.StockNumber)
                            {
                                distributionDataDTO.StockVolume = ShouldNumber;
                                ShouldNumber -= ShouldNumber;
                                distributionDataList.Add(distributionDataDTO);
                            }
                            else
                            {
                                distributionDataDTO.StockVolume = itemdata.StockNumber;
                                ShouldNumber -= itemdata.StockNumber;
                                distributionDataList.Add(distributionDataDTO);
                            }
                        }
                        else
                        {
                            break;
                        }
                    }
                }
            }
            return distributionDataList;
        }

        /// <summary>
        /// 分批次备货
        /// </summary>
        /// <param name="NoticeItemId"></param>
        /// <param name="Steps"></param>
        /// <returns></returns>
        public async Task<List<PickUpMaterialDTO>> AutomaticStepsData(string NoticeItemId, string Steps , RetrievalSeach data) {
            string yibei = Steps + "Ready";
            string sql = @"select T.* from 
(SELECT
	'{0}' AS StepIndex,
	`t1`.`NoticeId` AS `NoticeId`,
	`t1`.`DesignId` AS `DesignId`,
	`t1`.`MaterialId` AS `MaterialId`,
	`t1`.`PB_DesignListInfo` AS `PB_DesignListInfo`,
	`m`.`Name` AS `MaterialName`,
	`m`.`Code` AS `MaterialCode`,
	`m`.`Spec` AS `MaterialSpec`,
	`m`.`ProductCode` AS `ProductCode`,
	`m`.`UsageStatus` AS `UsageStatus`,
	IFNULL( m.Weight, 0 ) Weight,
	concat( `s`.`Name`, '/', `sk`.`Name`, '/', `l`.`Name` ) AS `StorAreaName`,
	`lm`.`StorId` AS `StorId`,
	`lm`.`LocalId` AS `LocationId`,
	lm.AreaId AS AreaId,
	lm.RackId AS RackId,
	`dt`.`Name` AS `StorageName`,
	`lm`.`Id` AS `Id`,
	IFNULL( `t1`.`{0}`, 0 ) AS `StepNumber`,
	IFNULL( `t1`.`RealNums`, 0 ) AS `RealNums`,
	CASE WHEN ifnull(( `lm`.`Nums_N`+`lm`.Nums_O - `lm`.Nums_R ), 0 )< 0 THEN
			0 ELSE ifnull(( `lm`.`Nums_N`+`lm`.Nums_O - `lm`.Nums_R ), 0 ) 
		END StockNumber,
	ifnull( `t1`.`{2}`, 0 ) AS `ReadyNums`,
	IFNULL( `t1`.{0}, 0 ) - IFNULL( `p`.`ReadyNums`, 0 ) AS StockVolume,
	ifnull(( CASE	WHEN (( `t1`.`{0}` - ifnull(  `p`.`ReadyNums`, 0 )) < 0 ) THEN 0 ELSE (	`t1`.`{0}` - ifnull( `p`.`ReadyNums`, 0 )) 	END 	),	0 	) AS `ShouldNumber`,(CASE	WHEN ( ifnull( concat( `t1`.`MaterialId`, '_', `lm`.`LocalId` ), '' ) = '' ) THEN
				`t1`.`MaterialId` ELSE concat( `t1`.`MaterialId`, '_', `lm`.`LocalId` ) END ) AS `onlyId` , grops.`Name` groupName FROM
			((((((((	SELECT `t`.`NoticeId` AS `NoticeId`,	`t`.`DesignId` AS `DesignId`,	`t`.`MaterialId` AS `MaterialId`,	sum( `t`.`RealNums` ) AS `RealNums`,	sum( `t`.`{0}` ) AS `{0}`,		sum( `t`.`{2}` ) AS `{2}`,	(		SELECT `PB_DesignDetail`.`Id` FROM 	`PB_DesignDetail` 	WHERE	((	`PB_DesignDetail`.`State` =0  	) 		AND ( `PB_DesignDetail`.`ParentId` = `t`.`DesignId` ) 	AND ( `PB_DesignDetail`.`MaterialId` = `t`.`MaterialId` )) 	ORDER BY	`PB_DesignDetail`.`CreateTime` 		LIMIT 1 		) AS `PB_DesignListInfo` 			FROM		(	SELECT
			`a`.`Id` AS `NoticeId`,	`a`.`DesignId` AS `DesignId`,	`b`.`MaterialId` AS `MaterialId`,		`b`.`{0}` AS `{0}`,		`b`.`{2}` AS `{2}`,		b.RealNums AS RealNums		FROM	(		`PB_DeliveryNoticeItem` `a`	JOIN `PB_DesignDetail` `b` ON ((
															`a`.`DesignId` = `b`.`ParentId` 
														))) 
											WHERE
												( `b`.`State` <> 2 ) UNION ALL
											SELECT
												`a`.`Id` AS `NoticeId`,
												`a`.`DesignId` AS `DesignId`,
												`b`.`MaterialId` AS `MaterialId`,-(
													`b`.`{0}` 
													) AS `{0}`,-(
													`b`.`{2}` 
												) AS `{2}`,
												-(
													b.RealNums 
												) AS RealNums
											FROM
												(
													`PB_DeliveryNoticeItem` `a`
													JOIN `PB_DesignDetail` `b` ON ((
															`a`.`DesignId` = `b`.`ParentId` 
														))) 
											WHERE
											( `b`.`State` = 2 )) `t` 
										GROUP BY
											`t`.`NoticeId`,
											`t`.`DesignId`,
											`t`.`MaterialId` 
											) `t1`
										LEFT JOIN `PB_Material` `m` ON ((
												`t1`.`MaterialId` = `m`.`Id` 
											)))
									LEFT JOIN `IT_LocalMaterial` `lm` ON ((
											`t1`.`MaterialId` = `lm`.`MaterialId` 
										)))
								LEFT JOIN `PB_Location` `l` ON ((
										`lm`.`LocalId` = `l`.`Id` 
									)))
                      	LEFT JOIN `Base_UnitItems` `grops` ON ((
									   	`m`.`storageId` = `grops`.`Id` 
									)))
							LEFT JOIN `PB_StorArea` `s` ON ((
									`l`.`AreaId` = `s`.`Id` 
								)))
						LEFT JOIN `PB_Rack` `sk` ON ((
								`l`.`RackId` = `sk`.`Id` 
							)))
					LEFT JOIN `Base_Department` `dt` ON ((
							`l`.`StorId` = `dt`.`Id` 
						)))
				LEFT JOIN (
				SELECT
					`a`.`ParentId` AS `ParentId`,
					`b`.`MaterialId` AS `MaterialId`,
					ifnull( sum( `b`.`ReadyNums` ), 0 ) AS `ReadyNums` 
				FROM
					(
						`PB_PickUpMain` `a`
						LEFT JOIN `PB_PickListSteps` `b` ON ((
								`a`.`Id` = `b`.`ParentId` 
							))) 
				WHERE
					( `a`.`Status` = 3 ) 
					AND b.StepIndex = '{0}' 
				GROUP BY
					`a`.`ParentId`,
					`b`.`MaterialId` 
					) `p` ON (((
							`t1`.`NoticeId` = `p`.`ParentId` 
							) 
						AND ( `t1`.`MaterialId` = `p`.`MaterialId` ) 
					))
		WHERE
			NoticeId = '{1}' 
			AND t1.{0} > 0 		";
            string storid = string.Empty;
            string matername = string.Empty;
            if (!data.StorId.IsNullOrEmpty()) {
                sql+= @"AND lm.StorId='"+data.StorId+"'";
            }
            //物料名称筛选
            if (!data.Matername.IsNullOrEmpty()) {
                sql+= @"AND (m.`Name` LIKE '%"+data.Matername+"%' OR m.`Code` LIKE '%"+data.Matername+"%') ";
            }
            //排序
            sql += @"ORDER BY `t1`.`PB_DesignListInfo`	) AS T  ";
            if (data.Another==true) {
                //查询最大库存
                sql += @"INNER JOIN ( SELECT Max(ifnull(( `Nums_N`+Nums_O - Nums_R ), 0 ) ) MAXCount,  ";
                sql += @"MaterialId ,StorId FROM IT_LocalMaterial GROUP BY MaterialId,StorId ) AS b ON T.MaterialId = b.MaterialId ";
                sql += @"WHERE  T.StockNumber=b.MAXCount AND b.StorId='" + data.StorId + "'  order By T.PB_DesignListInfo";
            }
            string sqlMain = string.Format(sql, Steps, NoticeItemId,yibei);
            var res =await Db.GetListBySqlAsync<PickUpMaterialDTO>(sqlMain);
            return res;
        }

        #endregion

        #region 私有成员

        #endregion
    }
}